package DAO;

import java.sql.ResultSet;
import java.util.ArrayList;

import POJO.Category;
import POJO.Photos;
import POJO.Product;
import Util.MySqlDataAccessHelper;

public class ProductDAO {
	public static ArrayList<Product> lstProduct(int pageNumber, int pageSize) {
		ArrayList<Product> lstProduct= new ArrayList<Product>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try {
			String sql =String.format("select products.ProductID,products.ProductName,products.Detail,products.CategoryId " +
					"FROM products " +
					//" ORDER by products.CategoryId  desc" +
					" LIMIT %d,%d",(pageNumber)*pageSize,pageSize);
			System.out.println("sql: "+sql);
			helper.open_en();
			ResultSet rs = helper.executeQuery(sql);
			while (rs.next()) {
			
				Product product=new Product();
				product.setProductId(rs.getInt("ProductID"));
				product.setProductName(rs.getString("ProductName"));
				product.setDetail(rs.getString("Detail"));
				product.setCategoryId(rs.getInt("CategoryId"));
				lstProduct.add(product);		
			}
		} catch (Exception ex) {
			ex.getMessage();
		}
		return lstProduct;
	}
	public static Product getProduct(int ProductId){
		Product product=null;
		 MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
	        try {
	            String sql = "select products.ProductID,products.ProductName,products.Detail," +
	            		"products.CategoryId,products.Color,products.Price " +
	            		"FROM products where products.ProductID="+ProductId;
	             	helper.open_en();         
	            ResultSet rs = helper.executeQuery(sql);
	            while (rs.next()) {
	            	product =new Product();
	            	product.setProductId(rs.getInt("ProductID"));
	            	product.setProductName(rs.getString("ProductName"));
	            	product.setDetail(rs.getString("Detail"));
	            	product.setCategoryId(rs.getInt("CategoryId"));
	            	product.setColor(rs.getString("Color"));
	            	product.setPrice(rs.getFloat("Price"));
            }
	        } catch (Exception e) {
	            System.out.println(e.getMessage());
	        } finally {
	            helper.close();
	        }
	        return product;
	    }
	public static ArrayList<Product> lstProductType(int idcate,int current, int pageSize) {
		System.out.println("pageSize: "+pageSize);
		System.out.println("current: "+current);
		ArrayList<Product> lstProduct= new ArrayList<Product>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try {
			String sql =String.format( "select products.ProductID,products.ProductName,products.Detail " +
					"FROM products,category " +
					"WHERE products.CategoryId=category.CategoryID  " +
					"and category.CategoryID="+idcate+" " +
					"group by products.ProductID LIMIT %d,%d ",3,pageSize);
			helper.open_en();
			ResultSet rs = helper.executeQuery(sql);
			while (rs.next()) {
				
				Product product=new Product();
				product.setProductId(rs.getInt("ProductID"));
				product.setProductName(rs.getString("ProductName"));
				product.setDetail(rs.getString("Detail"));
				
			
				lstProduct.add(product);		
			}
		} catch (Exception ex) {
			ex.getMessage();
		}
		return lstProduct;
	}
	public static ArrayList<Product> lstProduct_3() {
		ArrayList<Product> lstProduct= new ArrayList<Product>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try {
			String sql ="select products.ProductID,products.ProductName,products.Detail,products.CategoryId " +
					"FROM products " +
					" LIMIT 3";
			System.out.println("sql: "+sql);
			helper.open_en();
			ResultSet rs = helper.executeQuery(sql);
			while (rs.next()) {
			
				Product product=new Product();
				product.setProductId(rs.getInt("ProductID"));
				product.setProductName(rs.getString("ProductName"));
				product.setDetail(rs.getString("Detail"));
				product.setCategoryId(rs.getInt("CategoryId"));
				lstProduct.add(product);		
			}
		} catch (Exception ex) {
			ex.getMessage();
		}
		return lstProduct;
	}

}
